﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Linq;

using ECCIT;
using ECCIT.Data;
using ECCIT.Business.DAL;

using OgilvyOne.UKNow.Common;
using OgilvyOne.UKNow.Globalization;
using OgilvyOne.UKNow.Utility;
using OgilvyOne.UKNow.Model;
using OgilvyOne.UKNow.IDAL;

namespace OgilvyOne.UKNow.DAL
{
    public class EventsCommentDAL : UKNowBaseDAL, IDALEventsComment
    {
        private const string TBL_NAME = "UKNOW_EVENTS_COMMENT";
        private const string COL_LANG_ID = "COL_LANG_ID";
        private const string COL_COMMENT_ID = "COL_COMMENT_ID";
        private const string COL_EVENTS_ID = "COL_EVENTS_ID";
        private const string COL_MEMBER_ID = "COL_MEMBER_ID";
        private const string COL_COMMENT_TEXT = "COL_COMMENT_TEXT";
        private const string COL_COMMENT_TIME = "COL_COMMENT_TIME";
        private const string COL_COMMENT_IMG_PATH = "COL_COMMENT_IMG_PATH";
        private const string COL_COMMENT_IMG_NAME = "COL_COMMENT_IMG_NAME";
        private const string COL_COMMENT_IMG_URL = "COL_COMMENT_IMG_URL";
        private const string COL_COMMENT_IMG_DESC = "COL_COMMENT_IMG_DESC";
        private const string COL_COMMENT_IMG_TAG = "COL_COMMENT_IMG_TAG";
        private const string COL_COMMENT_VDO_PATH = "COL_COMMENT_VDO_PATH";
        private const string COL_COMMENT_VDO_URL = "COL_COMMENT_VDO_URL";
        private const string COL_COMMENT_VDO_NAME = "COL_COMMENT_VDO_NAME";
        private const string COL_COMMENT_VDO_DESC = "COL_COMMENT_VDO_DESC";
        private const string COL_COMMENT_VDO_TAG = "COL_COMMENT_VDO_TAG";
        private const string COL_SOURCE_MEMBER_ID = "COL_SOURCE_MEMBER_ID";
        private const string COL_COMMENT_STATE = "COL_COMMENT_STATE";



        public override string TableName { get { return TBL_NAME; } }

        public override Type ModelType { get { return typeof(EventsCommentModel); } }

        [PrimaryKey]
        public IField LanguageID { get { return FieldFactory.Create(ModelType, "LanguageID", ColumnFactory.CreateColumn(COL_LANG_ID, DataType.Int)); } }
        [PrimaryKey]
        public IField ID { get { return FieldFactory.Create(ModelType, "ID", ColumnFactory.CreateColumn(COL_COMMENT_ID, DataType.Int)); } }
        public IField EventsID { get { return FieldFactory.Create(ModelType, "EventsID", ColumnFactory.CreateColumn(COL_EVENTS_ID, DataType.Int)); } }
        public IField MemberID { get { return FieldFactory.Create(ModelType, "MemberID", ColumnFactory.CreateColumn(COL_MEMBER_ID, DataType.Int)); } }
        public IField Content { get { return FieldFactory.Create(ModelType, "Content", ColumnFactory.CreateColumn(COL_COMMENT_TEXT, DataType.String, 2000)); } }
        public IField Time { get { return FieldFactory.Create(ModelType, "Time", ColumnFactory.CreateColumn(COL_COMMENT_TIME, DataType.DateTime)); } }
        public IField ImagePath { get { return FieldFactory.Create(ModelType, "ImagePath", ColumnFactory.CreateColumn(COL_COMMENT_IMG_PATH, DataType.String, 200)); } }
        public IField ImageUrl { get { return FieldFactory.Create(ModelType, "ImageUrl", ColumnFactory.CreateColumn(COL_COMMENT_IMG_URL, DataType.String, 500)); } }
        public IField ImageName { get { return FieldFactory.Create(ModelType, "ImageName", ColumnFactory.CreateColumn(COL_COMMENT_IMG_NAME, DataType.String, 200)); } }
        public IField ImageDesc { get { return FieldFactory.Create(ModelType, "ImageDesc", ColumnFactory.CreateColumn(COL_COMMENT_IMG_DESC, DataType.String, 500)); } }
        public IField ImageTag { get { return FieldFactory.Create(ModelType, "ImageTag", ColumnFactory.CreateColumn(COL_COMMENT_IMG_TAG, DataType.String, 200)); } }
        public IField VideoPath { get { return FieldFactory.Create(ModelType, "VideoPath", ColumnFactory.CreateColumn(COL_COMMENT_VDO_PATH, DataType.String, 200)); } }
        public IField VideoUrl { get { return FieldFactory.Create(ModelType, "VideoUrl", ColumnFactory.CreateColumn(COL_COMMENT_VDO_URL, DataType.String, 500)); } }
        public IField VideoName { get { return FieldFactory.Create(ModelType, "VideoName", ColumnFactory.CreateColumn(COL_COMMENT_VDO_NAME, DataType.String, 200)); } }
        public IField VideoDesc { get { return FieldFactory.Create(ModelType, "VideoDesc", ColumnFactory.CreateColumn(COL_COMMENT_VDO_DESC, DataType.String, 500)); } }
        public IField VideoTag { get { return FieldFactory.Create(ModelType, "VideoTag", ColumnFactory.CreateColumn(COL_COMMENT_VDO_TAG, DataType.String, 200)); } }

        public IField SourceMemberID { get { return FieldFactory.Create(ModelType, "SourceMemberID", ColumnFactory.CreateColumn(COL_SOURCE_MEMBER_ID, DataType.Int)); } }
        public IField State { get { return FieldFactory.Create(ModelType, "STATE", ColumnFactory.CreateColumn(COL_COMMENT_STATE, DataType.Int)); } }

        public override IField[] InsertFields { get { return new IField[] { LanguageID, ID, EventsID, MemberID, Content, Time, ImagePath, ImageUrl, ImageName, ImageDesc, ImageTag, VideoPath, VideoUrl, VideoName, VideoDesc, VideoTag, SourceMemberID }; } }
        public override IField[] SelectFields { get { return new IField[] { LanguageID, ID, EventsID, MemberID, Content, Time, ImagePath, ImageUrl, ImageName, ImageDesc, ImageTag, VideoPath, VideoUrl, VideoName, VideoDesc, VideoTag, SourceMemberID, State }; } }
        public override IField[] UpdateFields { get { return InsertFields; } }

        public EventsCommentDAL() { }

        public EventsCommentDAL(LanguageType languageType) { this.Language = LanguageFactory.Create(languageType); }

        private void SetForeignKeyModel(EventsCommentModel model)
        {
            if (model == null) return;

            if (model.EventsID != null)
                model.Events = new EventsDAL(this.Language.LanguageType).GetEventsModelByID(model.EventsID);
            if (model.MemberID != null)
                model.Member = new MemberDAL().GetMemberModelByID(model.MemberID);
            if (model.SourceMemberID != null)
                model.SourceMember = new MemberDAL().GetMemberModelByID(model.SourceMemberID);

        }

        #region IDALEventsComment Members

        public EventsCommentModel GetEventsCommentModelByID(int? id)
        {
            EventsCommentModel model = (EventsCommentModel)GetModelByPrimaryKey(this.GetType(), new KeyValuePair<IField, object>[] { new KeyValuePair<IField, object>(LanguageID, (int)this.Language.LanguageType), new KeyValuePair<IField, object>(this.ID, id) });
            SetForeignKeyModel(model);
            return model;
        }

        public IList<EventsCommentModel> GetEventsCommentModelsByEventsID(int? eventsID)
        {
            string whereClause = string.Format("WHERE {0}={1} AND {2}={3}", COL_EVENTS_ID, eventsID, COL_LANG_ID, (int)this.Language.LanguageType);
            IList<EventsCommentModel> models = GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(Time.PropertyName, SortOrder.Desc))).List.Cast<EventsCommentModel>().ToList();
            foreach (EventsCommentModel model in models)
                SetForeignKeyModel(model);

            return models;
        }

        public PagingList GetEventsCommentModelsByEventsID(int? eventsID, PagingParameter pagingParams)
        {
            string whereClause = string.Format("WHERE {0}={1} AND {2}={3}", COL_EVENTS_ID, eventsID, COL_LANG_ID, (int)this.Language.LanguageType);
            if (pagingParams.SortingParameter == null)
                pagingParams.SortingParameter = SortingParameterFactory.CreateInstance(Time.PropertyName, SortOrder.Desc);
            
            PagingList models = GetModels(whereClause, pagingParams);
            foreach (EventsCommentModel model in models.List)
                SetForeignKeyModel(model);

            return models;
        }

        //mff
        /*修改人：穆凤凤
        * 修改时间：2012-04-18
        * 修改内容：
        *      新增方法 获得按用户ID,获得活动评论数据的方法 GetEventCommentByUserID,(用于接口)
        *      有mff注释的为本次修改的代码
        */
        //mff
        public IList<EventsCommentModel> GetEventCommentByUserID(int? userID)
        {
            string whereClause = string.Format("WHERE {0}={1} AND {2}={3}", COL_EVENTS_ID, userID, COL_LANG_ID, (int)this.Language.LanguageType);
            IList<EventsCommentModel> models = GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(Time.PropertyName, SortOrder.Asc))).List.Cast<EventsCommentModel>().ToList();
            foreach (EventsCommentModel model in models)
                SetForeignKeyModel(model);

            return models;
        }

        public List<EventComment> getModel(int? langID, int? userID, int CurrentPageIndex, int PageSize)
        {
            string sqlcmd = @"select COL_EVENTS_ID,COL_MEMBER_ID as uid,USERNAME   username,COL_COMMENT_IMG_NAME  ,COL_COMMENT_TEXT,COL_COMMENT_TIME 
                              from dbo.UKNOW_EVENTS_COMMENT as a join dbo.UKNOW_MEMBER_INFO as b on a.COL_MEMBER_ID=b.MEMBER_ID  ";

            if (langID.HasValue)
            {
                sqlcmd += "where a.COL_LANG_ID=" + langID;
                if (userID.HasValue)
                { sqlcmd += "and a.MEMBER_ID=" + userID; }
            }
            else
            {
                if (userID.HasValue)
                { sqlcmd += "where a.MEMBER_ID=" + userID; }
            }
            List<EventComment> list = new List<EventComment>();
            int totalRecordCount;
            DataSet ds = DBHelper.ExecuteDataSet(sqlcmd, CurrentPageIndex, PageSize, out totalRecordCount);

            if (ds.Tables[0].Rows.Count > 0)
            {
                //int i;
                DataTable dt = ds.Tables[0];

                foreach (DataRow dr in dt.Rows)
                {
                    EventComment model = new EventComment();
                    model.MEMBER_ID = int.Parse(dr["uid"].ToString());
                    model.USERNAME = dr["USERNAME"].ToString();
                    model.COL_EVENTS_ID = int.Parse(dr["COL_EVENTS_ID"].ToString());
                    model.COL_COMMENT_TEXT = dr["UKNOW_EVENTS_COMMENT"].ToString();
                    model.COL_COMMENT_IMG_NAME = dr["COL_COMMENT_IMG_NAME"].ToString();
                    model.COL_COMMENT_TIME = ((DateTime)dr["COL_COMMENT_TIME"]).ToString("yyyy/MM/dd HH:mm:ss");
                    list.Add(model);
                }

                return list;
            }
            else
            {
                return null;
            }
        }


        //mff
        public ResultInfo Save(EventsCommentModel modelInfo)
        {
            if (modelInfo == null) throw new ArgumentNullException("modelInfo");

            ResultInfo result = new ResultInfo(false);

            try
            {
                // 需要判断评论视频在flv_basic数据表中是否存在
                if (!string.IsNullOrEmpty(modelInfo.VideoName) && modelInfo.VideoName.ToLower().EndsWith(".flv"))
                {
                    string cmd = "select vid from flv_basic where vNamePath like '%" + modelInfo.VideoName.Replace(".flv", "") + "%'";
                    object obj = DBHelper.ExecuteScalar(cmd);
                    if (obj == null || obj == DBNull.Value)
                        modelInfo.VideoName = null;
                }

                int rowsAffected = 0;
                if (modelInfo.ID == null)   // insert
                {
                    modelInfo.ID = SqlUtil.GetMaxRecordID(TableName, COL_COMMENT_ID, COL_LANG_ID, (int)this.Language.LanguageType);
                    rowsAffected = Insert(modelInfo, null);
                }
                else // update
                {
                    rowsAffected = UpdateByPrimaryKey(modelInfo, null);
                }

                if (rowsAffected > 0)
                {
                    result.Message = "评论数据保存成功！";
                    result.Result = true;
                    result.ReturnObject = modelInfo;
                }
            }
            catch (Exception ex)
            {
                result.Exception = ex;
                result.Message = string.Format("保存评论数据时发生异常。异常信息：{0}", ex.Message);
            }

            return result;
        }

        public ResultInfo Delete(IList<int?> idList)
        {
            ResultInfo result = new ResultInfo(false);
            using (IDbConnection conn = DBHelper.GetDbConnection())
            {
                conn.Open();
                IDbTransaction trans = conn.BeginTransaction();
                try
                {
                    foreach (int? id in idList)
                    {
                        result = DeleteModelByPrimaryKey(trans, this.GetType(), new KeyValuePair<IField, object>[] { new KeyValuePair<IField, object>(LanguageID, (int)this.Language.LanguageType), new KeyValuePair<IField, object>(this.ID, id) });
                        if (result.Exception != null) throw result.Exception;
                    }

                    trans.Commit();
                    result.Result = true;
                    result.Message = "评论数据删除成功！";
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    result.Message = string.Format("删除评论数据时发生异常。异常信息：{0}", ex.Message);
                }
                finally
                {
                    trans.Dispose();
                }
            }

            return result;
        }

        public ResultInfo DeleteByEventsID(IDbTransaction trans, int? eventsID)
        {
            ResultInfo result = new ResultInfo(false);
            try
            {
                string whereClause = string.Format("where {0}={1} and {2}={3}", COL_LANG_ID, (int)this.Language.LanguageType, COL_EVENTS_ID, eventsID);
                Delete(trans, whereClause);
                result.Result = true;
                result.Message = Language.GetAlertMessage(ModelType, Enums.Action.Delete, Enums.ActionResult.Success);
            }
            catch (Exception ex)
            {
                result.Exception = ex;
                result.Message = Language.GetAlertMessage(ModelType, Enums.Action.Delete, Enums.ActionResult.Exception, ex);
            }

            return result;
        }

        public PagingList GetPublishedCommentsByEventsID(int? eventsID, PagingParameter pagingParams)
        {
            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_EVENTS_ID, eventsID, SearchConditionOperator.Equals));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));

            string whereClause = builder.ToSqlCommand();
            if (pagingParams == null)
                pagingParams = PagingParameterFactory.CreateNonPagingParameter(null);

            if (pagingParams.SortingParameter == null)  //如果没有排序参数，默认按照最后更新时间倒排序
                pagingParams.SortingParameter = SortingParameterFactory.CreateInstance(Time.PropertyName, SortOrder.Desc);

            PagingList list = GetModels(whereClause, pagingParams);
            foreach (EventsCommentModel model in list.List)
                SetForeignKeyModel(model);

            return list;
        }

        public int GetTotalCountByEventsID(int? eventsID)
        {
            string cmd = string.Format("select count({0}) from {1} where {2}={3} and {4}={5}", COL_COMMENT_ID, TableName, COL_LANG_ID, (int)this.Language.LanguageType, COL_EVENTS_ID, eventsID);
            object obj = DBHelper.ExecuteScalar(cmd);
            int totalCount = 0;
            if (obj != null && obj != DBNull.Value)
                totalCount = (int)obj;
            return totalCount;
        }

        public int GetTotalCountByUsereID(int? userID)
        {
            string cmd = string.Format("select count({0}) from {1} where {2}={3} and {4}={5}", COL_COMMENT_ID, TableName, COL_LANG_ID, (int)this.Language.LanguageType, COL_MEMBER_ID, userID);
            object obj = DBHelper.ExecuteScalar(cmd);
            int totalCount = 0;
            if (obj != null && obj != DBNull.Value)
                totalCount = (int)obj;
            return totalCount;
        }


        #endregion

        #region 根据用户ID获取该用户的评论数据
        /// <summary>
        /// 根据用户ID获取该用户的评论数据
        /// </summary>
        /// <param name="memberID">用户ID</param>
        /// <param name="pagingParams">分页参数</param>
        /// <returns>返回 PagingList</returns>
        public PagingList GetEventCommentsByMemberID(int? memberID, PagingParameter pagingParams)
        {
            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_MEMBER_ID, memberID, SearchConditionOperator.Equals));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));

            string whereClause = builder.ToSqlCommand();
            if (pagingParams == null)
                pagingParams = PagingParameterFactory.CreateNonPagingParameter(null);

            if (pagingParams.SortingParameter == null)  //如果没有排序参数，默认按照最后更新时间倒排序
                pagingParams.SortingParameter = SortingParameterFactory.CreateInstance(Time.PropertyName, SortOrder.Desc);

            PagingList list = GetModels(whereClause, pagingParams);

            foreach (EventsCommentModel model in list.List)
                SetForeignKeyModel(model);

            return list;
        }

        public PagingList getALLMod(PagingParameter pagingParams, int? userID)
        {
            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));
            if (userID.HasValue)
                builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_MEMBER_ID, userID.Value, SearchConditionOperator.Equals));

            if (pagingParams.SortingParameter == null)  //如果没有排序参数，默认按照最后更新时间倒排序
                pagingParams.SortingParameter = SortingParameterFactory.CreateInstance(Time.PropertyName, SortOrder.Desc);

            
            PagingList list = GetModels(builder.ToSqlCommand(), pagingParams);

            foreach (EventsCommentModel model in list.List)
                SetForeignKeyModel(model);

            return list;
        }

        public ResultInfo Update(EventsCommentModel model)
        {
            if (model == null) throw new ArgumentNullException("model");
            ResultInfo result = new ResultInfo(false);
            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_MEMBER_ID, model.MemberID,
                                                                        SearchConditionOperator.Equals));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType,
                                                                        SearchConditionOperator.Equals));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_COMMENT_ID, model.ID,
                                                                        SearchConditionOperator.Equals));

            string whereClause = builder.ToSqlCommand();

            // var sql = DBHelper.GetUpdateCommandText(TableName, new IField[] {Content}, whereClause);

            IDataParameter[] parms = DBHelper.GetDataParameters(new[] { Content, State, ImageName,VideoName });
            parms[0].Value = model.Content;
            parms[1].Value = 1;
            parms[2].Value = "";
            parms[3].Value = "";



            if (DBHelper.ExecuteNonQuery(DBHelper.GetUpdateCommandText(TableName, new IField[] { Content, State,ImageName, VideoName }, whereClause), parms) > 0)
            {
                result.Message = "删除成功!";
                result.Result = true;
            }

            return result;
        }

        #endregion

        public PagingList GetEventsCommentsWithImageByUserID(int? userID, PagingParameter pagingParams)
        {
            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_MEMBER_ID, userID, SearchConditionOperator.Equals));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(string.Format("isnull({0}, 'undefined')", COL_COMMENT_IMG_NAME), "undefined", SearchConditionOperator.NotEquals));

            string whereClause = builder.ToSqlCommand();
            if (pagingParams == null)
                pagingParams = PagingParameterFactory.CreateNonPagingParameter(null);

            if (pagingParams.SortingParameter == null)  //如果没有排序参数，默认按照最后更新时间倒排序
                pagingParams.SortingParameter = SortingParameterFactory.CreateInstance(Time.PropertyName, SortOrder.Desc);

            PagingList list = GetModels(whereClause, pagingParams);
            foreach (EventsCommentModel model in list.List)
                SetForeignKeyModel(model);

            return list;
        }
    }
}
